package com.excel.demo.excel.mapper;

import com.excel.demo.excel.model.ReadExcel;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author ljj
 * @since 2022-04-09
 */
@Mapper
public interface ReadExcelMapper extends EasyBaseMapper<ReadExcel> {
    @Update("delete from read_excel")
    Integer delectAll();

    @Select("select filename from read_excel where filename is not null and zcname is null limit 1")
    String selectFileName();

    @Select("select  cfaddress from read_excel where filename is null group by cfaddress")
    List<String> selectAllCfaddress();

    @Select("select xh,zcname,zcbianhao,zhangtao,kpnumber,zcnumber,bumen1,bumen2,user,zcstate,cfaddress,beizhu,gexh,jiage,remark from " +
            "read_excel where cfaddress = #{addr}")
    List<ReadExcel> selectReadExcelByAddr(String addr);

    @Select("select * frome read_excel where filename is null order by sheetindex,sheetname,xh ")
    List<ReadExcel> selectAllForTabel();

    @Insert("<script>" +
            "insert into read_excel values <foreach collection='list' item='re' index.html='index.html'  separator=',' >" +
            "(#{re.id},#{re.xh},#{re.zcname},#{re.zcbianhao},#{re.zhangtao},#{re.kpnumber},#{re.zcnumber},#{re.bumen1},#{re.bumen2},#{re.user},#{re.zcstate},#{re.cfaddress},#{re.beizhu},#{re.gexh},#{re.jiage},#{re.remark},#{re.filename},#{re.sheetname},#{re.sheetindex})" +
            "</foreach></script>")
    Integer insertBatch(List<ReadExcel> list);
}
